package com.useeinfo.framework.sugar.func.excel;

import com.useeinfo.framework.sugar.func.file.FileUtils;
import com.useeinfo.framework.sugar.tools.CommonSugar;
import com.useeinfo.framework.sugar.tools.StringConverters;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;

/**
 * Author: 居泽平  Date: 13-9-4, 上午9:26
 */
@SuppressWarnings("unused")
public class ExcelExport1 {

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    private static final Logger logger = LoggerFactory.getLogger(ExcelExport.class);

    /**
     * 导出Excel文件到指定路径下
     *
     * @param selectFieldMaps 选中需要导出的Field, title名称
     * @param jsonArray       最终导出数据的JSON对象
     * @throws java.io.IOException
     */
    public static String exportExcelFile(String finalXlsxPath, Integer i, Map<String, String> selectFieldMaps, JSONArray jsonArray, String configFileDirName, HttpServletRequest request) throws IOException {
        String contextPath = request.getServletContext().getRealPath("/");
        logger.info("获取得到Context原始根目录为:[{}]", contextPath);
        contextPath = contextPath.replace("\\", "/");
        File file1 = new File(contextPath);
        if (!file1.isAbsolute()) {
            file1 = new File(file1.getAbsolutePath());
        }
        contextPath = file1.getParent();
        contextPath += contextPath.endsWith("/") ? "" : "/";
        logger.info("获取得到FileUpload根目录为:[{}]", contextPath);

        if (!contextPath.endsWith("/")) {
            contextPath = contextPath + "/";
        }
        if (!configFileDirName.endsWith("/")) {
            configFileDirName = configFileDirName + "/";
        }
        configFileDirName += new SimpleDateFormat("yyyyMMdd").format(new Date());

        String fileName = configFileDirName + "/" + FileUtils.getFileNameByDateTime() + ".xls";
        String fileUrl = contextPath + fileName;

        copyFiles(finalXlsxPath, contextPath, fileName);

        File file = new File(fileUrl);
        if (!file.getParentFile().exists() && !file.isDirectory()) {
            file.getParentFile().mkdirs();
            file.createNewFile();
        } else {
            file.createNewFile();
        }

        String[] selectFields = new String[selectFieldMaps.size()];
        String[] selectFieldsName = new String[selectFieldMaps.size()];
        selectFieldMaps.keySet().toArray(selectFields);
        selectFieldMaps.values().toArray(selectFieldsName);

        Workbook wb = new HSSFWorkbook(new FileInputStream(file));
        ///其它要用到的地方也是同样的原理
        //主：如果只需要用到一次inputstream流，就不用这样啦，直接用就OK
        //创建工作簿
        Sheet sheet1 = wb.getSheetAt(0);//获取sheet

        //获取预先定义的样式
/*        Map<String, CellStyle> styleMap = ExcelExportStyle.getPredefinedCellStyle(wb);
        CellStyle cellStyleHead = styleMap.get("cellStyleHead");
        CellStyle cellStyleMain = styleMap.get("cellStyleMain");*/

        //外层数据行遍历
        Iterator iterator = jsonArray.iterator();
        while (iterator.hasNext()) {

            //初始化 Excel Row
            Row row = sheet1.createRow(i);
            row.setHeight((short) 540);

            //遍历的数据行元数据
            JSONObject jsonObject = (JSONObject) iterator.next();

            //内层字段遍历
            for (int j = 0; j < selectFields.length; j++) {
                String selectField = selectFields[j];

                //创建一个单元格
                Cell cell = row.createCell(j);
//                cell.setCellStyle(cellStyleMain);

                //判断如果当前字段名称在jsonObject中，则进行赋值操作
                if (jsonObject.keySet().toString().indexOf(selectField) > 0) {
                    //处理该字段的值
                    String selectValue = jsonObject.getString(selectField);
                    Double selectValueDouble = StringConverters.ToDouble(selectValue);
                    //如果纯数字，那么设置为数字格式
                    if (selectValue != null && selectValue.length() < 8 && selectValueDouble != null) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(selectValueDouble);
                    } else {
                        cell.setCellValue(selectValue);
                    }
                    //根据内容设定单元格宽度
                    int unitWidth = CommonSugar.isContainChinese(selectValue) ? 680 : 300;
                    int oldWidth = sheet1.getColumnWidth(j);
                    int newWidth = selectValue.length() * unitWidth;
                    if (newWidth > oldWidth) {
                        newWidth = newWidth > 14000 ? 14000 : newWidth;
                        sheet1.setColumnWidth(j, newWidth);
                    }
                }
            }

            //预备创建下一 Excel Row
            i++;
        }
        try {
            OutputStream out = new FileOutputStream(file);
            wb.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return fileName;
    }


    /**
     * 将指定路径下的文件复制到指定文件夹中
     */
    public static void copyFiles(String copyPath, String copiedFolderPath, String filename) {
        //被复制文件
        File copyFile = new File(copyPath);
        //指定文件夹
        File copiedFolder = new File(copiedFolderPath);

        try {
            //判断文件夹是否存在,不存在需要创建，否则无法正常创建该文件夹下的文件
            if (!copiedFolder.exists()) {
                System.out.println("copiedFolder not exists" + copiedFolderPath);
                copiedFolder.mkdirs();
            }
            //复制后文件的路径与命名
            String copiedFilePath = copiedFolderPath + filename;
            File copiedFile = new File(copiedFilePath);
            if (!copiedFile.exists()) {
                System.out.println("copiedFile not exists" + copiedFolderPath);
                copiedFile.createNewFile();
            }
            System.out.println(copiedFolderPath);
            //复制内容到指定文件中
            copyFileContent(copyFile, copiedFile);
        } catch (IOException e) {
            System.out.println("error" + e);
        }
    }

    /**
     * 将文件fromFile 的内容复制到  toFile文件中
     * 复制的可以是Excel等多种格式
     *
     * @param fromFile
     * @param toFile
     * @throws IOException
     */
    public static void copyFileContent(File fromFile, File toFile) throws IOException {
        FileInputStream ins = new FileInputStream(fromFile);
        FileOutputStream out = new FileOutputStream(toFile);
        byte[] b = new byte[1024];
        int n = 0;
        while ((n = ins.read(b)) != -1) {
            out.write(b, 0, n);
        }

        ins.close();
        out.close();
    }
}
